package com.freddy.sqlite.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.freddy.sqlite.FreddyChenApplication;
import com.freddy.sqlite.utils.BasicClass;

import java.util.LinkedList;
import java.util.List;

/**
 * Created by Freddy on 2015/12/3.
 * chenshichao@outlook.com
 * 数据库基类
 */
public class DataBaseHelper<T> extends SQLiteOpenHelper {

    private SQLiteDatabase mDefaultDatabase = null;

    private static final String TAG = DataBaseHelper.class.getSimpleName();

    /**
     * DataBaseHelper实例
     */
    private static DataBaseHelper INSTANCE;

    /**
     * 数据库名称
     */
    public static final String DATABASE_NAME = "freddy.db";

    /**
     * 数据库版本，从1开始，每升级一次递增1
     */
    public static final int DATABASE_VERSION = 1;

    protected Context mContext;

    /**
     * 单例模式
     * 双重校检锁
     * @return
     */
    public static DataBaseHelper getInstance() {
        if(null == INSTANCE) {
            synchronized (DataBaseHelper.class) {
                if(null == INSTANCE) {
                    INSTANCE = new DataBaseHelper(FreddyChenApplication.getInstance());
                }
            }
        }

        return INSTANCE;
    }

    /**
     * 私有构造方法，避免外部直接对本类进行实例化
     */
    private DataBaseHelper(Context context) {
        this(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * 必须的构造函数
     * @param context
     * @param name
     * @param factory
     * @param version
     */
    private DataBaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.mContext = context;
    }

    /**
     * 初始化数据库
     * 在调getReadableDatabase或getWritableDatabase时，会判断指定的数据库是否存在，不存在则调SQLiteDatabase.onCreate()创建，
     * onCreate只在数据库第一次创建时才执行，开发人员无需再自己判断是否表是否存在
     */
    public void initDatabase() {
        DataBaseHelper.getInstance().getRDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.mDefaultDatabase = db;
        createTable();
        Log.d(TAG, "Database is created...");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        this.mDefaultDatabase = db;
        // 如果非首次安装，且版本号发生变化，则执行数据迁移操作
        if(oldVersion > 0 && oldVersion < newVersion) {
            migrateTable();
        }
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        this.mDefaultDatabase = db;
    }

    /**
     * 创建表
     */
    private void createTable() {
        UserDB.getInstance(mContext).create();
    }

    /**
     * 创建表
     * 重载
     * @param tableName
     * @param columns
     */
    public void createTable(String tableName, String[] columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ");
        sql.append(tableName);
        sql.append(" (");
        sql.append(columns[0]);
        sql.append(" INTEGER PRIMARY KEY");

        for(int i = 1; null != columns && i < columns.length; i++) {
            sql.append(", ");
            sql.append(columns[i]);
            sql.append(" TEXT");
        }
        sql.append(");");

        mDefaultDatabase.execSQL(sql.toString());
    }

    /**
     * 删除表
     */
    private void dropTable() {
        UserDB.getInstance(mContext).drop();
    }

    /**
     * 删除表
     * 重载
     * @param tableName
     */
    public void dropTable(String tableName) {
        mDefaultDatabase.execSQL("DROP TABLE IF EXISTS " + tableName + ";");
    }

    /**
     * 迁移表
     */
    private void migrateTable() {
        UserDB.getInstance(mContext).migrate();
    }

    /**
     * 迁移表
     * 重载
     * @param officialTableName
     * @param tempTableName
     */
    public void migrateTable(String officialTableName, String tempTableName, String[] columns) {
        // 重命名表名为临时表
        alterTableName(officialTableName, tempTableName);
        // 创建新表
        createTable();

        // 数据迁移，从临时表查询旧数据迁移到新表，不存在的列用NULL补齐
        if(checkTableExists(tempTableName)) {
            StringBuilder sql = new StringBuilder("INSERT INTO " + officialTableName);
            sql.append(" SELECT ");

            for(int i = 0; null != columns && i < columns.length; i++) {
                // 拼接查询sql
                appendInsertColumnSql(sql, tempTableName, columns[i]);
            }

            // 移除最后的,号
//            sql.substring(0, sql.length() - 1);
            sql.setLength(sql.length() - 1);

            sql.append(" FROM " + tempTableName);
            mDefaultDatabase.execSQL(sql.toString());

            // 删除临时表
            dropTable(tempTableName);
        }
    }

    /**
     * 插入数据
     * @param tableName
     * @param values
     */
    public synchronized void insert(String tableName, ContentValues values) {
        DataBaseHelper.getInstance().getWDatabase().insert(tableName, null, values);
    }

    /**
     * 删除数据（单一条件）
     * @param tableName
     * @param whereClause
     * @param whereArg
     */
    public void delete(String tableName, String whereClause, String whereArg) {
        DataBaseHelper.getInstance().getWDatabase().delete(tableName, whereClause, new String[]{whereArg});
    }

    /**
     * 删除数据（多条件）
     * 重载
     * @param tableName
     * @param whereClause
     * @param whereArgs
     */
    public void delete(String tableName, String whereClause, String[] whereArgs) {
        DataBaseHelper.getInstance().getWDatabase().delete(tableName, whereClause, whereArgs);
    }

    /**
     * 删除表所有数据
     * @param tableName
     */
    public void deleteAll(String tableName) {
        StringBuilder sql = new StringBuilder("DELETE FROM ");
        sql.append(tableName);
        DataBaseHelper.getInstance().getWDatabase().execSQL(sql.toString());
    }

    /**
     * 更新（单一条件）
     * @param tabelName
     * @param values
     * @param whereClause
     * @param whereArg
     */
    public void update(String tabelName, ContentValues values, String whereClause, String whereArg) {
        this.update(tabelName, values, whereClause, new String[]{whereArg});
    }

    /**
     * 更新（多条件）
     * 重载
     * @param tableName
     * @param values
     * @param whereClause
     * @param whereArgs
     */
    public void update(String tableName, ContentValues values, String whereClause, String[] whereArgs) {
        DataBaseHelper.getInstance().getWDatabase().update(tableName, values, whereClause, whereArgs);
    }

    /**
     * 查询指定列的数据
     * @param tableName
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @return
     */
    public Cursor query(String tableName, String[] columns, String selection, String[] selectionArgs,
                        String groupBy, String having, String orderBy) {

        return DataBaseHelper.getInstance().getRDatabase().query(tableName, columns, selection,
                selectionArgs, groupBy, having, orderBy);
    }

    /**
     * 查询所有列的数据
     * @param tableName
     * @return
     */
    public Cursor query(String tableName) {
        return this.query(tableName, null, new String[]{});
    }

    /**
     * 查询所有列的数据
     * 重载
     * @param tableName
     * @param order
     * @param sort
     * @return
     */
    public Cursor query(String tableName, String order, boolean sort) {
        return this.query(tableName, null, new String[]{}, order, sort);
    }

    /**
     * 查询所有列的数据
     * 重载
     * @param tableName
     * @param whereClause
     * @param whereArg
     * @return
     */
    public Cursor query(String tableName, String whereClause, String whereArg) {
        return this.query(tableName, whereClause, new String[]{ whereArg });
    }

    /**
     * 查询所有列的数据
     * 重载
     * @param tableName
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public Cursor query(String tableName, String whereClause, String[] whereArgs) {
        return this.query(tableName, whereClause, whereArgs, null, false);
    }

    /**
     * 查询所有列的数据
     * 重载
     * @param tableName
     *                      表名
     * @param whereClause
     *                      筛选条件 如：id=?
     * @param whereArgs
     *                      筛选条件 ?所需参数值
     * @param order
     *                      排序列名
     * @param sort
     *                      排序方式，true表示升序，false表降序
     * @return
     */
    public Cursor query(String tableName, String whereClause, String[] whereArgs,
                        String order, boolean sort) {

        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        sql.append(tableName);

        if(!TextUtils.isEmpty(whereClause)) {
            sql.append(" WHERE ");
            sql.append(whereClause);
        }

        if(!TextUtils.isEmpty(order)) {
            sql.append(" ORDER BY ");
            sql.append(order);
            sql.append(sort ? " ASC" : " DESC");
        }

        return this.rawQuery(sql.toString(), whereArgs);
    }

    /**
     * 执行SQL语句查询
     * @param sql
     * @param selectionArgs
     * @return
     */
    public Cursor rawQuery(String sql, String[] selectionArgs) {
        return DataBaseHelper.getInstance().getRDatabase().rawQuery(sql, selectionArgs);
    }

    /**
     * 通过Cursor转换成对应的对象。注意：Cursor里的字段名（可用别名）必须要和对象的属性名一致
     * @param cursor
     * @param cls
     * @return
     */
    public T cursorToObject(Cursor cursor, Class<?> cls) {
        if(null == cursor) {
            return null;
        }

        T t;
        try {
            cursor.moveToNext();
            t = (T) BasicClass.setValuesToFields(cursor, cls);

            return t;
        } catch (Exception e) {
            e.printStackTrace();
            Log.d(TAG, "cursorToObject execute error!");
            return null;
        } finally {
            if(null != cursor) {
                cursor.close();
            }
        }
    }

    /**
     * 通过Cursor转换成对应的对象集合。注意：Cursor里的字段名（可用别名）必须要和对象的属性名一致
     * @param cursor
     * @param cls
     * @return
     */
    public List<T> cursorToList(Cursor cursor, Class<?> cls) {
        if(null == cursor) {
            return null;
        }

        List<T> objList = new LinkedList<T>();
        T t;
        try {
            while(cursor.moveToNext()) {
                t = (T) BasicClass.setValuesToFields(cursor, cls);
                objList.add(t);
            }

            return objList;
        } catch (Exception e) {
            e.printStackTrace();
            Log.d(TAG, "cursorToList execute error!");
            return null;
        } finally {
            if(null != cursor) {
                cursor.close();
            }
        }
    }

    /**
     * 根据条件，查询指定表是否存在符合条件的数据
     * @param tableName
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean isExitsByWhere(String tableName, String whereClause, String[] whereArgs) {
        return getCountByWhere(tableName, whereClause, whereArgs) > 0;
    }

    /**
     * 获取表数据总条数
     * @param tableName
     * @return
     */
    public int getCountByTableName(String tableName) {
        return getCountByWhere(tableName, null, new String[]{});
    }

    /**
     * 根据条件获取数据条数
     * @param tableName
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public int getCountByWhere(String tableName, String whereClause, String[] whereArgs) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM " + tableName);
        if(!TextUtils.isEmpty(whereClause)) {
            sql.append(" WHERE " + whereClause);
        }

        sql.append(";");
        Cursor cursor = this.rawQuery(sql.toString(), whereArgs);
        if(null != cursor) {
            cursor.moveToFirst();
            int count = cursor.getInt(0);
            Log.d(TAG, "count = " + count);
            cursor.close();
            return count;
        }

        return 0;
    }

    /**
     * 重命名表名
     * @param officialTableName
     * @param tempTableName
     */
    public void alterTableName(String officialTableName, String tempTableName) {
        if(TextUtils.isEmpty(officialTableName)) {
            return;
        }

        if(TextUtils.isEmpty(tempTableName)) {
            return;
        }

        if(!checkTableExists(officialTableName)) {
            return;
        }

        StringBuilder sql = new StringBuilder("ALTER TABLE ");
        sql.append(officialTableName);
        sql.append(" RENAME TO ");
        sql.append(tempTableName);

        mDefaultDatabase.execSQL(sql.toString());
    }

    /**
     * 检查指定表是否存在
     * @param tableName
     * @return
     */
    private boolean checkTableExists(String tableName) {
        StringBuilder sql = new StringBuilder("SELECT * FROM SQLITE_MASTER");
        sql.append(" WHERE NAME = ");
        sql.append("'");
        sql.append(tableName);
        sql.append("'");
        sql.append(";");

        Cursor cursor = this.rawQuery(sql.toString(), null);
        if(null != cursor) {
            return cursor.getCount() != 0;
        }

        return false;
    }

    /**
     * 拼接插入sql语句
     * @param sql
     * @param tableName
     * @param columnName
     */
    private void appendInsertColumnSql(StringBuilder sql, String tableName, String columnName) {
        if(isColumnExists(tableName, columnName)) {
            sql.append(columnName);
        }else {
            sql.append("NULL");
        }

        sql.append(",");
    }

    /**
     * 检查表中指定列是否存在
     * @param tableName
     * @param columnName
     * @return
     */
    private boolean isColumnExists(String tableName, String columnName) {
        boolean result = false;
        StringBuilder sql = new StringBuilder("SELECT * FROM SQLITE_MASTER");
        sql.append(" WHERE NAME = ?");
        sql.append(" AND SQL LIKE ?");
        Cursor cursor = this.rawQuery(sql.toString(), new String[]{ tableName, "%" + columnName + "%" });
        if(null != cursor) {
            result = cursor.moveToFirst();
            cursor.close();
        }

        return result;
    }

    /**
     * 获取临时表名
     */
    public String getTempTableName(String tableName) {
        return tableName + "_Temp";
    }

    /**
     * 单例化WritableDatabase()
     * @return
     */
    public SQLiteDatabase getWDatabase() {
        return DataBaseHelper.getInstance().getWritableDatabase();
    }

    /**
     * 单例化ReadableDatabase()
     * @return
     */
    public SQLiteDatabase getRDatabase() {
        return DataBaseHelper.getInstance().getReadableDatabase();
    }

    /**
     * 问题分析：
     * app的db相关的操作SQLiteOpenHelper的实现类中，app在public void onCreate(SQLiteDatabase db)
     * 或者是public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)中
     * 在其他线程或者是在这些方法体有其他的db模块调用到了getWritableDatabase进行db的新增，修改，删除等操作就会导致此问题
     *
     * 解决方案：
     * 如果在onCreate或者是onUpdate的过程中有调用到其他模块进行相同db操作，则需要进行类似于下面内容的处理
     *
     * getReadableDatabase()同理
     * @see http://www.myexception.cn/database/1584296.html
     * @return
     */
    @Override
    public SQLiteDatabase getWritableDatabase() {
        if(null == mDefaultDatabase){
            mDefaultDatabase = super.getWritableDatabase();
        }

        return mDefaultDatabase;
    }

    @Override
    public SQLiteDatabase getReadableDatabase() {
        if(null == mDefaultDatabase){
            mDefaultDatabase = super.getReadableDatabase();
        }

        return mDefaultDatabase;
    }

    /**
     * 数据库结构
     */
    public static class DataBaseColumns {
        public static String id = "_id";

        public static String[] getColumns() {
            return new String[]{ id };
        }
    }
}
